<?php
include_once "src/controller/dbmanagement/DBConnection.php";
include_once "src/rrsexception/DBException.php";
include_once "src/rrsexception/DBConnectionException.php";
/* 
 * This class generates Report
 *
 * @author: Ngozi Ihemelandu
 * 
 */


  class ReportDBManagement
  {

        /**
        * This report shows the frequency of use of the Resources By Users over a
        * specified period of time
        *
        *Precondition:  Accepts two (2) variables for a specified period.
        *Format for mysql Date is (YYYY/MM/DD)
        *
        *Postcondition: Returns a multidimensional Array that shows the:
        *                  Resource
        *                  Frequency Of Use
        *                  Resource Owner
        *               Otherwise returns False.
        **/

      public static function RptResourceUsedByUsersInPeriod($startPeriod, $endPeriod)
      {

          $cnn = new DBConnection();

          try
          {

             $query =  "SELECT A.Name Resource,
                               count(A.Resource_id) Frequency_Of_Use,
                               C.Username User
                        FROM tResource A, tReservation B, tUser C
                        WHERE A.Resource_id = B.Resource_id
                        AND C.User_id = B.User_id
                        AND B.Start_date BETWEEN '$startPeriod' AND '$endPeriod'
                        GROUP BY C.User_id, A.Name
                        ORDER BY C.User_id";

              $result = $cnn->execute($query);

              $numRows = mysql_numrows($result);

               if ($numRows == 0)
               {
                    $cnn->disconnect();
                    return false;
               }
               else
               {
                   for ($x = 0; $x < $numRows; $x++)
                   {
                       $row = mysql_fetch_array($result);

                       $g[$x][0] = $row['Resource'];
                       $g[$x][1] = $row['Frequency_Of_Use'];
                       $g[$x][2] = $row['User'];

                    }

               $cnn->disconnect();
               return $g;
               }

            }
            catch (DBException $e) {
                throw $e;
            }
            catch (DBConnectionException $e) {
                throw $e;
            }

      }

      public static function RptResourceUsedByUsersInPeriod2($startPeriod, $endPeriod)
      {

          $cnn = new DBConnection();

          try
          {

             $query =  "SELECT A.Name Resource,
                               count(A.Resource_id) Frequency_Of_Use,
                               C.Username User
                        FROM tResource A, tReservation B, tUser C
                        WHERE A.Resource_id = B.Resource_id
                        AND C.User_id = B.User_id
                        AND B.Start_date BETWEEN '$startPeriod' AND '$endPeriod'
                        GROUP BY C.User_id, A.Name
                        ORDER BY A.Resource_id";

              $result = $cnn->execute($query);

              $numRows = mysql_numrows($result);

               if ($numRows == 0)
               {
                    $cnn->disconnect();
                    return false;
               }
               else
               {
                   for ($x = 0; $x < $numRows; $x++)
                   {
                       $row = mysql_fetch_array($result);

                       $g[$x][0] = $row['Resource'];
                       $g[$x][1] = $row['Frequency_Of_Use'];
                       $g[$x][2] = $row['User'];

                    }

               $cnn->disconnect();
               return $g;
               }

            }
            catch (DBException $e) {
                throw $e;
            }
            catch (DBConnectionException $e) {
                throw $e;
            }

      }

     /**
      * This report shows the frequency of use of the Resources over a
      * specified period of time
      *
      *Precondition:  Accepts two (2) variables for a specified period.
      *Format for mysql Date is (YYYY/MM/DD)
      *
      *Postcondition: Returns a multidimensional Array that shows the:
      *                  Resource
      *                  Frequency Of Use
      *                  Resource Owner
      *               Otherwise returns False.
      **/

      public static function RptResourceUsedInPeriod($startPeriod, $endPeriod)
      {

          $cnn = new DBConnection();

          try
          {

             $query =  "SELECT A.Name Resource,
                               count(A.Resource_id) Frequency_Of_Use,
                               C.Username Owner
                        FROM tResource A, tReservation B, tUser C
                        WHERE A.Resource_id = B.Resource_id
                        AND A.Owner_id = C.User_id
                        AND B.Start_date BETWEEN '$startPeriod' AND '$endPeriod'
                        GROUP BY A.Resource_id";

              $result = $cnn->execute($query);

              $numRows = mysql_numrows($result);

               if ($numRows == 0)
               {
                    $cnn->disconnect();
                    return false;
               }
               else
               {
                   for ($x = 0; $x < $numRows; $x++)
                   {
                       $row = mysql_fetch_array($result);

                       $ArrayRcrd[$x][0] = $row['Resource'];
                       $ArrayRcrd[$x][1] = $row['Frequency_Of_Use'];
                       $ArrayRcrd[$x][2] = $row['Owner'];

                    }

                    $cnn->disconnect();
                    return $ArrayRcrd;
               }

            }
            catch (DBException $e) {
                throw $e;
            }
            catch (DBConnectionException $e) {
                throw $e;
            }

      }

  }

?>
